From 3c43394ca176a239f958aeee4d44e6b029efb94e Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 13 Aug 2026 16:47:17 +0000 Subject: [PATCH] Enforce, or not, COMM_IDS.Studied on COMM_MEMBS --- .../lib/triggers/create/biography_data.m4 | 12 +++-- db/schemas/lib/triggers/create/comm_membs.m4 | 51 +++++++++++-------- 2 files changed, 37 insertions(+), 26 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 41a68ff..e4b801c 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -357,8 +357,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () END; END IF; - -- An individual may not be placed in a community, that is not their - -- birth community, before the individual is under study. + -- An individual may not be placed in a study community, that is not + -- their birth community, before the individual is under study. IF TG_OP = 'UPDATE' AND ((NEW.entrydate > OLD.entrydate) OR (NEW.birthcomm IS DISTINCT FROM OLD.birthcomm)) THEN @@ -369,17 +369,19 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT comm_membs.startdate, comm_membs.commmid INTO startdate, commmid FROM comm_membs + JOIN comm_ids ON (comm_ids.commid = comm_membs.commid) WHERE comm_membs.animid = NEW.animid AND comm_membs.commid IS DISTINCT FROM NEW.birthcomm AND comm_membs.startdate < NEW.entrydate + AND comm_ids.studied ORDER BY comm_membs.startdate LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' - , DETAIL = 'An individual may not be placed in a community,' - || ' that is not their birth community, before' - || ' the individual is under study' + , DETAIL = 'An individual may not be placed in a studied' + || ' community, that is not their birth community,' + || ' before the individual is under study' || ': Key (AnimID) = (' || NEW.animid || '), Value (AnimName) = (' diff --git a/db/schemas/lib/triggers/create/comm_membs.m4 b/db/schemas/lib/triggers/create/comm_membs.m4 index c02fcbc..7f59722 100644 --- a/db/schemas/lib/triggers/create/comm_membs.m4 +++ b/db/schemas/lib/triggers/create/comm_membs.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2023-2025 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2023-2026 The Meme Factory, Inc. http://www.karlpinc.com/ dnl dnl This program is free software: you can redistribute it and/or modify it dnl under the terms of the GNU Affero General Public License as published by @@ -32,7 +32,7 @@ CREATE OR REPLACE FUNCTION comm_membs_func () BEGIN -- Function for comm_membs insert and update triggers -- - -- AGPL_notice(` --', `2023-2025', + -- AGPL_notice(` --', `2023-2026', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN @@ -163,27 +163,36 @@ CREATE OR REPLACE FUNCTION comm_membs_func () END IF; -- An individual may not be placed in a community, in which - -- they were not born, before the individual is under study. + -- they were not born, before the individual is under study, + -- when the community is a study community. IF NEW.commid IS DISTINCT FROM a_birthcomm AND NEW.startdate < a_entrydate THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' - , DETAIL = 'An individual may not be placed in a community,' - || ' excepting their birth community,' - || ' before the individual is under study' - || ': Key (CommMID) = (' - || NEW.commmid - || '), Value (AnimID) = (' - || NEW.animid - || '), Value (CommID) = (' - || NEW.commid - || '), Value (StartDate) = (' - || NEW.startdate - || '), Value (BIOGRAPHY_DATA.BirthComm) = (' - || textualize(`a_birthcomm') - || '), Value (BIOGRAPHY_DATA.EntryDate) = (' - || a_entrydate - || ')'; + + PERFORM 1 + FROM comm_ids + WHERE comm_ids.commid = NEW.commid + AND comm_ids.studied; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'An individual may not be placed in a study community,' + || ' excepting their birth community,' + || ' before the individual is under study' + || ': Key (CommMID) = (' + || NEW.commmid + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (CommID) = (' + || NEW.commid + || '), Value (StartDate) = (' + || NEW.startdate + || '), Value (BIOGRAPHY_DATA.BirthComm) = (' + || textualize(`a_birthcomm') + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || ')'; + END IF; END IF; -- An individual may not be placed in a community after the -- 2.34.1